package com.bbva.recaudos.dao;

import java.io.IOException;
import java.io.Reader;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.bbva.recaudos.bean.RegistroIntercnx;
import com.bbva.recaudos.util.Constantes;


public class RegistroIntercnxDAOOracle implements RegistroIntercnxDAO
{
	
	SqlSessionFactory sqlMapper = null;
	SqlSessionFactory sqlMapperCali = null;
	SqlSessionFactory sqlMapperProd = null;
	
	{
		try {
			Reader reader  = Resources.getResourceAsReader(Constantes.IBATIS_CONFIG_FILE_NAME);
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			sqlMapper 	  = builder.build(reader,Constantes.NOMBRE_AMBIENTE_TEST);
			reader  = Resources.getResourceAsReader(Constantes.IBATIS_CONFIG_FILE_NAME);
			sqlMapperCali = builder.build(reader,Constantes.NOMBRE_AMBIENTE_CALI);
			reader  = Resources.getResourceAsReader(Constantes.IBATIS_CONFIG_FILE_NAME);
			sqlMapperProd = builder.build(reader,Constantes.NOMBRE_AMBIENTE_PROD);
		} catch (IOException ex)
		{
			ex.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerTodos() throws Exception {
		SqlSession session = sqlMapper.openSession();
		List<RegistroIntercnx> lista = null;
		try
		{
			lista = session.selectList("intercnx.SQL_selectAll");
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerRegsAuditV01(RegistroIntercnx filtro)
			throws Exception {

		SqlSession session = sqlMapper.openSession();
		List<RegistroIntercnx> lista = null;
		try
		{
			lista = session.selectList("intercnx.SQL_getRegsAuditv01", filtro);
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return lista;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerRegsAuditV01(RegistroIntercnx filtro, int amb)
			throws Exception {

		SqlSession session = null;
		List<RegistroIntercnx> lista = null;
		
		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			lista = session.selectList("intercnx.SQL_getRegsAuditv01", filtro);
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return lista;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerRegsAuditV02(RegistroIntercnx filtro)
			throws Exception {

		SqlSession session = sqlMapper.openSession();
		List<RegistroIntercnx> lista = null;
		try
		{
			lista = session.selectList("intercnx.SQL_getRegsAuditv02", filtro);
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return lista;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerRegsAuditV02(RegistroIntercnx filtro, int amb)
			throws Exception {

		SqlSession session = null;
		List<RegistroIntercnx> lista = null;

		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			lista = session.selectList("intercnx.SQL_getRegsAuditv02", filtro);
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return lista;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<RegistroIntercnx> obtenerRegsAuditV02XNumOpe(Integer numeroOperacion, int amb)
			throws Exception {

		SqlSession session = null;
		List<RegistroIntercnx> lista = null;

		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			lista = session.selectList("intercnx.SQL_getRegsAuditv02XNumOpe", numeroOperacion);
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return lista;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public RegistroIntercnx obtenerMsjAuditV02(RegistroIntercnx filtro, int amb)
			throws Exception
	{
		SqlSession session = null;
		RegistroIntercnx bean = null;
		//String mensaje = null;

		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			bean = (RegistroIntercnx) session.selectOne("intercnx.SQL_getMsjAuditv02", filtro);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return bean;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public byte[] obtenerMsjAuditOld(RegistroIntercnx filtro, int amb)
			throws Exception
	{
		SqlSession session = null;
		oracle.sql.BLOB blob = null;
		HashMap map = null;
		byte[] bytes = null;

		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			map = (HashMap) session.selectOne("intercnx.SQL_getMsjAuditOld", filtro);
			blob = (oracle.sql.BLOB) map.get("MENSAJE");
			bytes = blob.getBytes(1L, (int)blob.length());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return bytes;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public String obtenerMsjAuditNew(RegistroIntercnx filtro, int amb)
			throws Exception
	{
		SqlSession session = null;
		String mensaje = null;
		//String mensaje = null;

		if(amb==Constantes.AMBIENTE_TEST)
			session = sqlMapper.openSession();
		else if(amb==Constantes.AMBIENTE_CALI)
			session = sqlMapperCali.openSession();
		else 
			session = sqlMapperProd.openSession();
		
		try
		{
			mensaje = session.selectOne("intercnx.SQL_getMsjAuditNew", filtro).toString();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return mensaje;
	}
	
	//*****************************
	//MAIN TESTs
	//*****************************
	
	public static void main(String args[])
	{
		testGetClobCol();
	}
	
	private static void testFormatearXML()
	{
		RegistroIntercnxDAO dao = new RegistroIntercnxDAOOracle();
		RegistroIntercnx bean1 = null;
		RegistroIntercnx bean = new RegistroIntercnx();
		bean.setFecha("2014-02-13%");
		bean.setNumeroConvenio(1463);
		
		List<RegistroIntercnx> lista = null;
		
		/*
		// Instantiate transformer input
		StreamResult xmlOutput = new StreamResult(new StringWriter());
		Source xmlInput = null;
		Transformer transformer = null; 
		//transformer.setOutputProperty(OutputKeys.DOCTYPE_SYSTEM, "testing.dtd");
		

		try
		{
			
			//lista = dao.obtenerRegsAuditV02(bean, Constantes.AMBIENTE_TEST);
			//lista = dao.obtenerRegsAuditV02XNumOpe(52841, Constantes.AMBIENTE_TEST);
			//bean1 = dao.obtenerMsjAuditV02(filtro, Constantes.AMBIENTE_TEST);
			
			//set XML input
			xmlInput = new StreamSource(new StringReader(bean1.getMensaje()));
			
			// Configure transformer
			transformer = TransformerFactory.newInstance().newTransformer(); // An identity transformer
			transformer.setOutputProperty(OutputKeys.INDENT, "yes");
//			transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
//			transformer.setOutputProperty(OutputKeys.ENCODING, "Windows-1252");
			transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
			transformer.transform(xmlInput, xmlOutput);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		//System.out.println(lista.size());
		System.out.println("mensaje: [" + bean1.getMensaje() + "]");
		System.out.println("mensaje pretty\n" + xmlOutput.getWriter().toString());
		*/
	}
	
	private static void testGetBlobCol()
	{
		RegistroIntercnxDAO dao = new RegistroIntercnxDAOOracle();
		RegistroIntercnx filtro = new RegistroIntercnx();
		filtro.setMsgId("C3E2D840D4D7C4F14040404040404040C9B9AAA5EB28F605");
		filtro.setIdEvento("2");
		
		byte[] bytes = null;
		
		try
		{
			bytes = dao.obtenerMsjAuditOld(filtro, Constantes.AMBIENTE_TEST);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println(new String(bytes)); //use for text data
		System.out.println(Arrays.toString(bytes));
		
	}
	
	private static void testGetClobCol()
	{
		RegistroIntercnxDAO dao = new RegistroIntercnxDAOOracle();
		RegistroIntercnx filtro2 = new RegistroIntercnx();
		filtro2.setNumeroOperacion(55183);
		filtro2.setIdEvento("2");
		
		String mensaje = null;
		
		try
		{
			mensaje = dao.obtenerMsjAuditNew(filtro2, Constantes.AMBIENTE_TEST);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("mensaje-->[" + mensaje + "]");
		
	}
}
